import psycopg2
from psycopg2 import sql
import sys

# 数据库连接配置
DB_CONFIG = {
    "dbname": "game_trading_v2",
    "user": "postgres",
    "password": "145114",
    "host": "localhost",
    "port": "5432"
}

def connect_to_db():
    """连接到数据库并返回连接对象"""
    try:
        conn = psycopg2.connect(**DB_CONFIG)
        return conn
    except Exception as e:
        print(f"数据库连接失败: {e}")
        sys.exit(1)

def execute_query(conn, query, description, params=None):
    """执行查询并处理结果"""
    cur = conn.cursor()
    try:
        if params:
            cur.execute(query, params)
        else:
            cur.execute(query)
        conn.commit()
        print(f"✅ {description}成功创建")
        return True
    except Exception as e:
        conn.rollback()
        print(f"❌ {description}创建失败: {e}")
        print(f"错误详情: {str(e)}")
        return False
    finally:
        cur.close()

def create_views(conn):
    """创建所有视图"""
    print("\n=== 创建数据库视图 ===")
    
    # 1. 活跃装备视图
    active_equipment_view = """
    CREATE OR REPLACE VIEW ActiveEquipmentView AS
    SELECT e.EquipmentID, e.EquipmentName, e.Category, e.Level, e.Rarity, 
           e.Price, e.ListedDate, u.Username AS SellerName, u.UserID AS SellerID
    FROM Equipments e
    JOIN Users u ON e.SellerID = u.UserID
    WHERE e.Status = 'active';
    """
    execute_query(conn, active_equipment_view, "活跃装备视图(ActiveEquipmentView)")
    
    # 2. 用户交易视图
    user_transactions_view = """
    CREATE OR REPLACE VIEW UserTransactionsView AS
    SELECT t.TransactionID, t.TransactionDate, t.Price,
           e.EquipmentID, e.EquipmentName, e.Category, 
           s.Username AS SellerName, s.UserID AS SellerID,
           b.Username AS BuyerName, b.UserID AS BuyerID
    FROM Transactions t
    JOIN Equipments e ON t.EquipmentID = e.EquipmentID
    JOIN Users s ON t.SellerID = s.UserID
    JOIN Users b ON t.BuyerID = b.UserID;
    """
    execute_query(conn, user_transactions_view, "用户交易视图(UserTransactionsView)")
    
    # 3. 用户钱包摘要视图
    wallet_summary_view = """
    CREATE OR REPLACE VIEW UserWalletSummary AS
    SELECT u.UserID, u.Username, u.Balance,
           COUNT(CASE WHEN w.Type = 'deposit' THEN 1 END) AS DepositCount,
           COUNT(CASE WHEN w.Type = 'withdraw' THEN 1 END) AS WithdrawCount,
           COUNT(CASE WHEN w.Type IN ('income','expense') THEN 1 END) AS TransactionCount
    FROM Users u
    LEFT JOIN WalletRecords w ON u.UserID = w.UserID
    GROUP BY u.UserID, u.Username, u.Balance;
    """
    execute_query(conn, wallet_summary_view, "用户钱包摘要视图(UserWalletSummary)")
    
    # 4. 用户装备视图
    user_equipment_view = """
    CREATE OR REPLACE VIEW UserEquipmentView AS
    SELECT e.EquipmentID, e.EquipmentName, e.Category, e.Level, e.Rarity, 
           e.Price, e.ListedDate, e.Status, u.UserID, u.Username
    FROM Equipments e
    JOIN Users u ON e.SellerID = u.UserID;
    """
    execute_query(conn, user_equipment_view, "用户装备视图(UserEquipmentView)")

def create_triggers(conn):
    """创建所有触发器"""
    print("\n=== 创建数据库触发器 ===")
    
    # 首先创建触发器函数
    
    # 1. 价格变动触发器函数
    price_change_trigger_func = """
    CREATE OR REPLACE FUNCTION price_change_notify()
    RETURNS TRIGGER AS $$
    BEGIN
        IF OLD.Price != NEW.Price THEN
            INSERT INTO PriceChangeMessages (UserID, EquipmentID, OldPrice, NewPrice, ChangeDate, IsRead)
            SELECT c.UserID, NEW.EquipmentID, OLD.Price, NEW.Price, NOW(), FALSE
            FROM Cart c
            WHERE c.EquipmentID = NEW.EquipmentID AND c.UserID != NEW.SellerID;
        END IF;
        RETURN NEW;
    END;
    $$ LANGUAGE plpgsql;
    """
    execute_query(conn, price_change_trigger_func, "价格变动触发器函数")
    
    # 价格变动触发器
    price_change_trigger = """
    DROP TRIGGER IF EXISTS equipment_price_change_trigger ON Equipments;
    CREATE TRIGGER equipment_price_change_trigger
    AFTER UPDATE OF Price ON Equipments
    FOR EACH ROW
    EXECUTE FUNCTION price_change_notify();
    """
    execute_query(conn, price_change_trigger, "价格变动触发器")
    
    # 2. 装备下架触发器函数
    inactive_equipment_func = """
    CREATE OR REPLACE FUNCTION remove_from_cart_on_inactive()
    RETURNS TRIGGER AS $$
    BEGIN
        IF NEW.Status = 'inactive' AND OLD.Status = 'active' THEN
            DELETE FROM Cart WHERE EquipmentID = NEW.EquipmentID;
        END IF;
        RETURN NEW;
    END;
    $$ LANGUAGE plpgsql;
    """
    execute_query(conn, inactive_equipment_func, "装备下架触发器函数")
    
    # 装备下架触发器
    inactive_equipment_trigger = """
    DROP TRIGGER IF EXISTS equipment_inactive_trigger ON Equipments;
    CREATE TRIGGER equipment_inactive_trigger
    AFTER UPDATE OF Status ON Equipments
    FOR EACH ROW
    EXECUTE FUNCTION remove_from_cart_on_inactive();
    """
    execute_query(conn, inactive_equipment_trigger, "装备下架触发器")
    
    # 3. 交易完成触发器函数
    transaction_complete_func = """
    CREATE OR REPLACE FUNCTION process_completed_transaction()
    RETURNS TRIGGER AS $$
    DECLARE
        buyer_balance NUMERIC(10,2);
        seller_balance NUMERIC(10,2);
    BEGIN
        -- 更新装备状态为已售出
        UPDATE Equipments 
        SET Status = 'sold' 
        WHERE EquipmentID = NEW.EquipmentID;
        
        -- 买家扣款
        UPDATE Users 
        SET Balance = Balance - NEW.Price 
        WHERE UserID = NEW.BuyerID
        RETURNING Balance INTO buyer_balance;
        
        -- 卖家收款
        UPDATE Users 
        SET Balance = Balance + NEW.Price 
        WHERE UserID = NEW.SellerID
        RETURNING Balance INTO seller_balance;
        
        -- 生成买家支出记录
        INSERT INTO WalletRecords
            (UserID, TransactionID, Type, Amount, Balance, Timestamp)
        VALUES
            (NEW.BuyerID, NEW.TransactionID, 'expense', -NEW.Price, buyer_balance, NOW());
        
        -- 生成卖家收入记录
        INSERT INTO WalletRecords
            (UserID, TransactionID, Type, Amount, Balance, Timestamp)
        VALUES
            (NEW.SellerID, NEW.TransactionID, 'income', NEW.Price, seller_balance, NOW());
        
        -- 从购物车中移除已购买装备
        DELETE FROM Cart 
        WHERE UserID = NEW.BuyerID AND EquipmentID = NEW.EquipmentID;
        
        RETURN NEW;
    END;
    $$ LANGUAGE plpgsql;
    """
    execute_query(conn, transaction_complete_func, "交易完成触发器函数")
    
    # 交易完成触发器
    transaction_complete_trigger = """
    DROP TRIGGER IF EXISTS transaction_complete_trigger ON Transactions;
    CREATE TRIGGER transaction_complete_trigger
    AFTER INSERT ON Transactions
    FOR EACH ROW
    WHEN (NEW.Status = 'completed')
    EXECUTE FUNCTION process_completed_transaction();
    """
    execute_query(conn, transaction_complete_trigger, "交易完成触发器")

def create_procedures(conn):
    """创建所有存储过程"""
    print("\n=== 创建数据库存储过程 ===")
    
    # 1. 购物车结算存储过程
    checkout_cart_proc = """
    CREATE OR REPLACE PROCEDURE checkout_cart(
        buyer_id INT, 
        equipment_ids INT[],
        OUT success BOOLEAN,
        OUT message TEXT
    )
    LANGUAGE plpgsql
    AS $$
    DECLARE
        total_price NUMERIC(10,2) := 0;
        current_balance NUMERIC(10,2);
        equip_record RECORD;
        trans_id INT;
    BEGIN
        -- 获取用户余额
        SELECT Balance INTO current_balance FROM Users WHERE UserID = buyer_id;
        
        -- 计算总价并检查装备状态
        FOR equip_record IN 
            SELECT e.EquipmentID, e.Price, e.SellerID, e.Status
            FROM Equipments e
            WHERE e.EquipmentID = ANY(equipment_ids)
        LOOP
            IF equip_record.Status != 'active' THEN
                success := FALSE;
                message := '装备状态不可购买: ' || equip_record.EquipmentID;
                RETURN;
            END IF;
            
            total_price := total_price + equip_record.Price;
        END LOOP;
        
        -- 检查余额
        IF current_balance < total_price THEN
            success := FALSE;
            message := '余额不足，需要: ' || total_price || '，当前余额: ' || current_balance;
            RETURN;
        END IF;
        
        -- 处理每个装备的交易
        FOR equip_record IN 
            SELECT e.EquipmentID, e.Price, e.SellerID
            FROM Equipments e
            WHERE e.EquipmentID = ANY(equipment_ids)
        LOOP
            -- 创建交易记录
            INSERT INTO Transactions
                (EquipmentID, SellerID, BuyerID, TransactionDate, Price, Status)
            VALUES 
                (equip_record.EquipmentID, equip_record.SellerID, buyer_id, NOW(), equip_record.Price, 'completed')
            RETURNING TransactionID INTO trans_id;
        END LOOP;
        
        success := TRUE;
        message := '结算成功，总金额: ' || total_price;
    EXCEPTION
        WHEN OTHERS THEN
            success := FALSE;
            message := '结算失败: ' || SQLERRM;
    END;
    $$;
    """
    execute_query(conn, checkout_cart_proc, "购物车结算存储过程")
    
    # 2. 充值存储过程
    deposit_proc = """
    CREATE OR REPLACE PROCEDURE deposit_funds(
        user_id INT, 
        amount NUMERIC(10,2),
        OUT success BOOLEAN,
        OUT message TEXT
    )
    LANGUAGE plpgsql
    AS $$
    DECLARE
        new_balance NUMERIC(10,2);
    BEGIN
        IF amount <= 0 THEN
            success := FALSE;
            message := '充值金额必须大于0';
            RETURN;
        END IF;
        
        -- 更新余额
        UPDATE Users 
        SET Balance = Balance + amount 
        WHERE UserID = user_id
        RETURNING Balance INTO new_balance;
        
        -- 创建流水记录
        INSERT INTO WalletRecords
            (UserID, Type, Amount, Balance, Timestamp)
        VALUES 
            (user_id, 'deposit', amount, new_balance, NOW());
        
        success := TRUE;
        message := '充值成功，当前余额: ' || new_balance;
    EXCEPTION
        WHEN OTHERS THEN
            success := FALSE;
            message := '充值失败: ' || SQLERRM;
    END;
    $$;
    """
    execute_query(conn, deposit_proc, "充值存储过程")
    
    # 3. 提现存储过程
    withdraw_proc = """
    CREATE OR REPLACE PROCEDURE withdraw_funds(
        user_id INT, 
        amount NUMERIC(10,2),
        OUT success BOOLEAN,
        OUT message TEXT
    )
    LANGUAGE plpgsql
    AS $$
    DECLARE
        current_balance NUMERIC(10,2);
        new_balance NUMERIC(10,2);
    BEGIN
        IF amount <= 0 THEN
            success := FALSE;
            message := '提现金额必须大于0';
            RETURN;
        END IF;
        
        -- 检查余额
        SELECT Balance INTO current_balance FROM Users WHERE UserID = user_id;
        
        IF current_balance < amount THEN
            success := FALSE;
            message := '余额不足，当前余额: ' || current_balance;
            RETURN;
        END IF;
        
        -- 扣减余额
        UPDATE Users 
        SET Balance = Balance - amount 
        WHERE UserID = user_id
        RETURNING Balance INTO new_balance;
        
        -- 创建流水记录
        INSERT INTO WalletRecords
            (UserID, Type, Amount, Balance, Timestamp)
        VALUES 
            (user_id, 'withdraw', -amount, new_balance, NOW());
        
        success := TRUE;
        message := '提现成功，当前余额: ' || new_balance;
    EXCEPTION
        WHEN OTHERS THEN
            success := FALSE;
            message := '提现失败: ' || SQLERRM;
    END;
    $$;
    """
    execute_query(conn, withdraw_proc, "提现存储过程")
    
    # 4. 交易报表生成存储过程
    report_proc = """
    CREATE OR REPLACE PROCEDURE generate_transaction_report(
        start_date TIMESTAMP,
        end_date TIMESTAMP,
        OUT transaction_count INT,
        OUT total_amount NUMERIC(12,2)
    )
    LANGUAGE plpgsql
    AS $$
    DECLARE
        cur CURSOR FOR 
            SELECT t.TransactionID, t.Price
            FROM Transactions t
            WHERE t.TransactionDate BETWEEN start_date AND end_date
              AND t.Status = 'completed'
            ORDER BY t.TransactionDate;
        
        rec RECORD;
    BEGIN
        transaction_count := 0;
        total_amount := 0;
        
        OPEN cur;
        
        LOOP
            FETCH cur INTO rec;
            EXIT WHEN NOT FOUND;
            
            transaction_count := transaction_count + 1;
            total_amount := total_amount + rec.Price;
        END LOOP;
        
        CLOSE cur;
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE '生成报表失败: %', SQLERRM;
    END;
    $$;
    """
    execute_query(conn, report_proc, "交易报表生成存储过程")

def main():
    """主函数"""
    print("开始创建数据库视图、触发器和存储过程...")
    
    conn = connect_to_db()
    
    try:
        create_views(conn)
        create_triggers(conn)
        create_procedures(conn)
        
        print("\n✅ 所有操作完成")
    finally:
        conn.close()

if __name__ == "__main__":
    main()